package com.wanhai.base.dao;

import com.wanhai.bean.OracleModuleBean;
import com.wanhai.ejb.dbhandler.DBHandlerLocal;
import com.wanhai.util.RecordProcessBean;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.object.SqlFunction;

public class BaseDAO implements IBaseDAO {

    @Autowired
    private DataSource dataSource;
    JdbcTemplate jdbcTemplate;
    protected static Logger logger = Logger.getLogger(BaseDAO.class);

    public BaseDAO() {
    }

    public int updateBean(String sql, Object... parems) throws Exception {
        System.out.println("dataSource :" + dataSource);
        jdbcTemplate = new JdbcTemplate(dataSource);
        int result = jdbcTemplate.update(sql, parems);

        return result;
    }

//    public void callStoredProcedure(){
//
//        SimpleJdbcCall procReadActor;
//
//        procReadActor = new SimpleJdbcCall(dataSource).withProcedureName("read_actor");
//        
//        SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
//        Map out = procReadActor.execute(in);
//        Actor actor = new Actor();
//        actor.setId(id);
//        actor.setFirstName((String) out.get("out_first_name"));
//        actor.setLastName((String) out.get("out_last_name"));
//        actor.setBirthDate((Date) out.get("out_birth_date"));
//        
//
//    }   
    /**
     * add db connection information
     *
     * @param conn
     * @param om
     * @param isClear
     * @throws SQLException
     */
    public void call_sp_was_0001(boolean isClear) throws SQLException {//Connection conn,OracleModuleBean om,boolean isClear) throws SQLException, Exception{


//         CallableStatement cs=null;
        try {
            OracleModuleBean om = JndiNameHolder.getOracleModuleBean();

            if (om != null) {
                Map<String, Object> params = new HashMap<String, Object>();
                params.put("p_function", om.getFunction());
                params.put("p_program_code", om.getProgram_code());
                params.put("p_web_info", om.getWeb_info());
                params.put("p_user_code", om.getUser_code());
                params.put("p_err_code", 0);
                params.put("p_message", "ok");
                Map<String, Object> result = callStoredProcedure("sp_was_0001", params);
                if (result.get("P_ERR_CODE") != null && result.get("P_ERR_CODE").equals("99")) {
                    throw new SQLException((String) result.get("P_MESSAGE"));
                }
            } else if (om == null && isClear == true) {
                Map<String, Object> params = new HashMap<String, Object>();
                params.put("p_function", "C");
                params.put("p_program_code", "");
                params.put("p_web_info", "");
                params.put("p_user_code", "");
                params.put("p_err_code", 0);
                params.put("p_message", "ok");
                Map<String, Object> result = callStoredProcedure("sp_was_0001", params);
                if (result.get("P_ERR_CODE") != null && result.get("P_ERR_CODE").equals("99")) {
                    throw new SQLException((String) result.get("P_MESSAGE"));
                }
            }


            //                if(om!=null){
//                    //set
//                    cs = conn.prepareCall("{call sp_was_0001(?,?,?,?,?,?)}");
//                    cs.setString(1,om.getFunction());
//                    cs.setString(2,om.getProgram_code());
//                    cs.setString(3,om.getWeb_info());
//                    cs.setString(4,om.getUser_code());
//                    cs.registerOutParameter(5, Types.VARCHAR);
//                    cs.registerOutParameter(6, Types.VARCHAR);
//                    cs.execute();
//                   
//                }else if(om==null && isClear ==true){
//                    //clear
//                    cs = conn.prepareCall("{call sp_was_0001(?,?,?,?,?,?)}");
//                    cs.setString(1,"C");
//                    cs.setString(2,null);
//                    cs.setString(3,null);
//                    cs.setString(4,null);
//                    cs.registerOutParameter(5, Types.VARCHAR);
//                    cs.registerOutParameter(6, Types.VARCHAR);
//                    cs.execute();
//                    if(cs.getString(5)!=null && cs.getString(5).equals("99")){
//                        throw new SQLException(cs.getString(6));
//                    }
//                }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new SQLException(ex);
        }
//finally{
//                if(cs!=null)try{cs.close();}catch(Exception e){}
//            }
    }
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * Query for Bean Return Bean
     *
     * @param sql
     * @param T
     * @param params
     * @return
     * @throws Exception
     */
    public <T1> T1 queryForBean(String sql, Class<T1> T1) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<T1> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<T1>(T1));

        if (result == null || result.size() == 0) {
            return null;
        }
        return result.get(0);
    }

    /**
     * Query for Bean Return Bean
     *
     * @param sql
     * @param T
     * @param params
     * @return
     * @throws Exception
     */
    public <T1> T1 queryForBean(String sql, Class<T1> T1, Object... params) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<T1> result = jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<T1>(T1));

        if (result == null || result.size() == 0) {
            return null;
        }
        return result.get(0);
    }

    /**
     *
     * @param sql
     * @param T
     * @param bean
     * @return
     * @throws Exception
     */
    public <T1> T1 queryForBean(String sql, Class<T1> T1, SqlParameterSource bean) throws Exception {
        namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        List<T1> result = namedParameterJdbcTemplate.query(sql, bean, new BeanPropertyRowMapper<T1>(T1));

        if (result == null || result.size() == 0) {
            return null;
        }
        return result.get(0);
    }

    /**
     * Query Return Map
     *
     * @param sql
     * @param T
     * @param params
     * @return
     * @throws Exception
     */
    public Map<String, Object> queryForMap(String sql) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);

        if (result == null || result.size() == 0) {
            return null;
        }
        return result.get(0);
    }

    /**
     * Query Return Map
     *
     * @param sql
     * @param T
     * @param params
     * @return
     * @throws Exception
     */
    public Map<String, Object> queryForMap(String sql, Object... params) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, params);

        if (result == null || result.size() == 0) {
            return null;
        }
        return result.get(0);
    }

    /**
     * Query For Bean return Bean List
     *
     * @param sql
     * @param T
     * @param params
     * @return
     * @throws Exception
     */
    public <T1> List<T1> queryForBeanList(String sql, Class<T1> T1) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<T1> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<T1>(T1));

        if (result == null || result.size() == 0) {
            return null;
        }
        return result;
    }

    /**
     * Query For Bean return Bean List
     *
     * @param sql
     * @param T
     * @param params
     * @return
     * @throws Exception
     */
    public <T1> List<T1> queryForBeanList(String sql, Class<T1> T1, Object... params) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<T1> result = jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<T1>(T1));

        if (result == null || result.size() == 0) {
            return null;
        }
        return result;
    }

    /**
     * User Spring JDBCTemplet maping bean to Query return Bean List
     *
     * ex. y
     *
     * @param sql
     * @param T
     * @param bean
     * @return
     * @throws Exception
     */
    public <T1> List<T1> queryForBeanList(String sql, Class<T1> T1, SqlParameterSource bean) throws Exception {
        namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        List<T1> result = namedParameterJdbcTemplate.query(sql, bean, new BeanPropertyRowMapper<T1>(T1));

        if (result == null || result.size() == 0) {
            return null;
        }
        return result;
    }

    /**
     * User Spring JDBCTemplet to Query return Map List
     *
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryForMapList(String sql) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);

        if (result == null || result.size() == 0) {
            return null;
        }
        return result;
    }

    /**
     * User Spring JDBCTemplet to Query for params return Map List
     *
     * @param sql
     * @param params
     * @return List<Map<String,Object>>
     * @throws Exception
     */
    public List<Map<String, Object>> queryForMapList(String sql, Object... params) throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, params);

        if (result == null || result.size() == 0) {
            return null;
        }
        return result;
    }

    /**
     * Use in name Call Procedure
     *
     * ex. call procedure{in id, in name}
     *
     * Map.put("id","a0001") Map.put("name", "Mark")
     *
     * @param procedureName
     * @param params
     * @return
     */
    public Map<String, Object> callStoredProcedure(String procedureName, Map<String, Object> params) throws Exception {
        Map<String, Object> result = new HashMap<String, Object>();
        try {
            jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.setSkipUndeclaredResults(true);
            SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withSchemaName("whpr").withProcedureName(procedureName);
            SqlParameterSource in = new MapSqlParameterSource(params);
            result = simpleJdbcCall.execute(in);
        } catch (Exception e) {
            logger.error("callStoredProcedure has Error, procedureName : " + procedureName + " ,params :" + params + " ,Exception :" + e);
            throw e;
        }

        return result;
    }

    /**
     * Use in name Call Procedure
     *
     * ex. call procedure{in id, in name}
     *
     * Map.put("id","a0001") Map.put("name", "Mark")
     *
     * @param procedureName
     * @param params
     * @return
     */
    public Map<String, Object> callStoredProcedure(String procedureName, Map<String, Object> params, SqlParameter... paramType) throws Exception {

        Map<String, Object> result = new HashMap<String, Object>();

        try {
            jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.setSkipUndeclaredResults(true);
            SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName(procedureName).declareParameters(paramType);
            SqlParameterSource in = new MapSqlParameterSource(params);
            result = simpleJdbcCall.execute(in);
        } catch (Exception e) {
            logger.error("callStoredProcedure has Error, procedureName : " + procedureName + " ,params :" + params + " ,Exception :" + e);
            throw e;
        }

        return result;
    }

    public String callFunction(String functionSql, int[] typeArray, Object... params) throws Exception {
        String result = null;
        try {
//	        String functionSql = "SELECT devuser.fnc_FntGetDealBtoB(?, ?, ?) AS BackToBack ";
            SqlFunction function = new SqlFunction(dataSource, functionSql);
            function.setTypes(typeArray);
            function.compile();
            result = (String) function.runGeneric(params);

        } catch (Exception e) {
            logger.error("String result has Error, functionSql : " + functionSql + " ,params :" + params + " ,Exception :" + e);
            throw e;
        }
        return result;
    }

    // add by ryu 2015/03/10
    public <T extends Object> T callFunction(String procedureName, Map<String, Object> params, Class<T> returnType)
            throws Exception {
        T value;
        SimpleJdbcCall simpleJdbcCall;
        try {
            jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.setSkipUndeclaredResults(true);
            if (procedureName.indexOf(".") != -1) {
                String catalogName = procedureName.substring(0, procedureName.indexOf("."));
                String functionName = procedureName.substring(procedureName.indexOf(".") + 1);
                simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withCatalogName(catalogName).withFunctionName(
                        functionName);
            } else {
                simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName(procedureName);
            }
            SqlParameterSource in = new MapSqlParameterSource(params);
            value = simpleJdbcCall.executeFunction(returnType, in);

        } catch (Exception e) {
            logger.error("callFunction has Error, procedureName : " + procedureName + " ,params :" + params
                    + " ,Exception :" + e);
            throw e;
        }
        return value;
    }

    public String Convert(String strSql) {
        if (strSql == null || strSql.length() == 0) {
            return null;
        }

        String resultSql = "";
        String tokens1[] = strSql.split("!");
        resultSql = tokens1[0] + resultSql;
        for (int i = 1; i < tokens1.length; i++) {

            if (!tokens1[i].trim().isEmpty()) {
//                    System.out.println("tokens1 "+tokens1[i]);
                ArrayList<String> data = new ArrayList<String>();
                String tokens2[] = tokens1[i].split("@");

                for (int X = 0; X < tokens2.length; X++) {
                    if (!tokens2[X].trim().isEmpty()) {
//                                    System.out.println("tokens2 "+tokens2[X]);	
                        data.add(tokens2[X]);
                    }
                }
//                    System.out.println("data :"+data.size());
                if (data.size() == 2) {
                    resultSql = resultSql + " " + data.get(0);
//                            System.out.println("data.get(1).indexOf('>') "+data.get(1).indexOf(">") );
                    //TODO:檢查格式是否正確
                    if (data.get(1).indexOf("%") > 0) {
                        resultSql = resultSql + " like '" + data.get(1).trim() + "'";
                    } else if (data.get(1).indexOf(">=") > -1 || data.get(1).indexOf("<=") > -1) {
                        String dataType = checDataType(data.get(1).trim().substring(2));
                        if ("int".equals(dataType)) {
                            resultSql = resultSql + " " + data.get(1).trim();
                        } else {
                            String tmp1 = data.get(1).trim();
                            resultSql = resultSql + " " + tmp1.substring(0, 2) + " '" + tmp1.substring(2).trim() + "'";
                        }
                    } else if (data.get(1).indexOf(">") > -1 || data.get(1).indexOf("<") > -1) {
//                                    System.out.println("data.get(1).trim().substring(1)"+data.get(1).trim().substring(1));
                        String dataType = checDataType(data.get(1).trim().substring(1));
                        if ("int".equals(dataType)) {
                            resultSql = resultSql + " " + data.get(1).trim();
                        } else {
                            String tmp1 = data.get(1).trim();
                            resultSql = resultSql + " " + tmp1.substring(0, 1) + " '" + tmp1.substring(1).trim() + "'";
                        }
                    } else if (data.get(1).indexOf("=") > -1) {
                        String tmp1 = data.get(1).trim().substring(1);
                        String dataType = checDataType(data.get(1).trim());
                        if ("int".equals(dataType)) {
                            resultSql = resultSql + " = " + tmp1;
                        } else {
                            resultSql = resultSql + " = '" + tmp1 + "'";
                        }
                    } else {
                        String tmp1 = data.get(1).trim();
                        String dataType = checDataType(data.get(1).trim());
                        if ("int".equals(dataType)) {
                            //TODO:解決數字與字串問題
//                                            resultSql = resultSql+ " = " + tmp1;
                            resultSql = resultSql + " = '" + tmp1 + "'";

                        } else {
                            resultSql = resultSql + " = '" + tmp1 + "'";
                        }
                    }
                }
            }
        }

        System.out.println("resultSql :" + resultSql);
        return resultSql;
    }

    public String checDataType(String data) {
        System.out.println("checDataType data :" + data);
        try {
            Double.parseDouble(data);

        } catch (java.lang.NumberFormatException e) {
//            e.printStackTrace();
            return "string";
        }

        return "int";
    }

    public String convDateToString(Date date, String format) {
        String dateStr = "";
        try {
            if (date != null) {
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                dateStr = sdf.format(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return dateStr;
    }

    /* DBHandler features implementation below**************************************************************************************/
    @Override
    public int update(RecordProcessBean parameter) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        return local.update(parameter);
    }

    @Override
    public int delete(RecordProcessBean parameter) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        return local.delete(parameter);
    }

    @Override
    public <T> List<T> query(RecordProcessBean parameter, Class<T> clz) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        return (List<T>) local.query(parameter);
    }

    @Override
    public <T> List<T> query(RecordProcessBean parameter, Class<T> clz, String[] column) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        return (List<T>) local.query(parameter, parameter.tableName());
    }

    @Override
    public <T> List<T> query(RecordProcessBean parameter, Class<T> clz, String[] column, String where, String orderby) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        if (where != null) {
            local.setWhere(where);
        }
        if (orderby != null) {
            local.setOrderBy(orderby);
        }
        return (List<T>) local.query(parameter, parameter.tableName());
    }

    @Override
    public <T> List<T> partialQuery(RecordProcessBean parameter, Class<T> clz, int pageNum, int pageSize) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        return (List<T>) local.partialQuery(parameter, pageNum, pageSize);
    }

    @Override
    public <T> List<T> partialQuery(RecordProcessBean parameter, Class<T> clz, int pageNum, int pageSize, String[] column) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        return (List<T>) local.partialQuery(parameter, pageNum, pageSize, column);
    }

    @Override
    public <T> List<T> partialQuery(RecordProcessBean parameter, Class<T> clz, int pageNum, int pageSize, String[] column, String where, String orderby) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        if (where != null) {
            local.setWhere(where);
        }
        if (orderby != null) {
            local.setOrderBy(orderby);
        }
        return (List<T>) local.partialQuery(orderby, pageNum, pageSize, column);
    }

    @Override
    public <T> T queryFirstRecord(RecordProcessBean parameter, Class<T> clz) throws Exception {
        DBHandlerLocal local = new DBHandlerLocal();
        local.setConnection(dataSource.getConnection());
        return (T) local.queryFirstRecord(parameter);
    }
}
